IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetAdvanceFundRequest]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetAdvanceFundRequest]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


--GetAdvanceFundRequest 41359,'Afr.DateSubmitted',5,0,1

CREATE PROCEDURE [dbo].[GetAdvanceFundRequest] 
(  
	@fwkDomainUserId	INT,
	@OrderBy VARCHAR(70) = NULL,
	@PageSize INT = 0,
	@PageStartIndex INT = 0,
	@PageEndIndex INT = 1  
)  
AS 

SET NOCOUNT ON
BEGIN  

			IF (@OrderBy IS NULL OR @OrderBy = '') SET @OrderBy = 'Afr.DateSubmitted DESC'
						
		    DECLARE	@RecordCount         INT
			DECLARE @strSql NVARCHAR(4000)
			SET @strSql = ''

			--return the request detail
			SELECT @RecordCount = COUNT(*) FROM  dbo.AdvanceFundRequest AS Afr	
			INNER JOIN dbo.agragreement_fct AS af ON af.foldernumber = Afr.PropNumber
			LEFT OUTER JOIN dbo.AdmPerson AS adm ON adm.FwkDomainUserId = Afr.SubmittedBy 
			INNER JOIN dbo.UserResourceCache AS urc ON af.ResourceId = urc.ResourceId AND urc.resourcetypeID = 1 AND urc.fwkdomainuserid =  @fwkDomainUserId 					  

			IF @PageSize = 0 SET @PageSize = @RecordCount	
					
			SET @strSql = 'WITH PROPGrid AS ('	
		  	SET @strSql = @strSql + 'SELECT '
			SET @strSql = @strSql + 'Afr.PropNumber AS ''PropNumber'','
			SET @strSql = @strSql + 'Afr.CreatedBy AS ''CreatedBy'','
			SET @strSql = @strSql + 'CONVERT(VARCHAR(10), Afr.DateSubmitted,101) AS ''DateSubmitted'','
            SET @strSql = @strSql + 'adm.LastName' + ' + '' '' + ' + 'adm.FirstName' + ' AS ''SubmittedBy'',' 
            SET @strSql = @strSql + '1 + DATEDIFF(day,Afr.DateSubmitted,CASE WHEN (SELECT COUNT(*) FROM dbo.AdvanceFundRequestWorkFlowStatus AS awf WHERE awf.AdvanceFundRequestId = Afr.Id AND (awf.AdvanceFundRequestStatusTypeId = 5 OR awf.AdvanceFundRequestStatusTypeId = 6)) > 0 THEN (SELECT awf.DateStart FROM dbo.AdvanceFundRequestWorkFlowStatus AS awf WHERE awf.AdvanceFundRequestId = Afr.Id AND (awf.AdvanceFundRequestStatusTypeId = 5 OR awf.AdvanceFundRequestStatusTypeId = 6))  ELSE GETDATE() END) AS ''DaysToComplete'',' 
			SET @strSql = @strSql + '(SELECT TOP 1 CONVERT(VARCHAR(10), aeh.DateCreated,101) FROM dbo.AdvanceFundRequestEventHistory AS aeh WHERE aeh.AdvanceFundRequestId = Afr.Id ORDER BY aeh.DateCreated DESC)  AS ''DateLastUpdate'','
			SET @strSql = @strSql + 'af.PILastName' + ' + '' '' + ' + 'af.PIFirstName' + ' AS ''PI'','
			SET @strSql = @strSql + 'admPI.FwkDomainUserId AS ''PIId'','
			SET @strSql = @strSql + 'admCId.FwkDomainUserId AS ''ChiefId'','
			SET @strSql = @strSql + 'admDId.FwkDomainUserId AS ''DelegateId'','
			--SET @strSql = @strSql + '182029 AS ''DelegateId'','
			SET @strSql = @strSql + 'admGId.FwkDomainUserId AS ''GAId'','
			SET @strSql = @strSql + 'af.SponsorName AS ''Sponsor'','
			SET @strSql = @strSql + 'af.Institution AS ''Hospital'','
			SET @strSql = @strSql + 'af.ChiefCode AS ''COS'','
			SET @strSql = @strSql + '(SELECT TOP 1 [NAME] FROM dbo.AdvanceFundRequestStatusType AS ast INNER JOIN dbo.AdvanceFundRequestWorkFlowStatus AS awf ON awf.AdvanceFundRequestStatusTypeId = ast.Id WHERE awf.AdvanceFundRequestId = Afr.Id ORDER BY awf.AdvanceFundRequestStatusTypeId DESC) AS ''RequestStatus'','
			SET @strSql = @strSql + '(SELECT TOP 1 AdvanceFundRequestStatusTypeId FROM dbo.AdvanceFundRequestWorkFlowStatus AS awf WHERE awf.AdvanceFundRequestId = Afr.Id ORDER BY awf.AdvanceFundRequestStatusTypeId DESC) AS ''RequestStatusType'','
			SET @strSql = @strSql +	'ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') AS ROWNUM,'
			SET @strSql = @strSql +	CAST(@RecordCount AS NVARCHAR(10)) + ' AS RecordCount '	
            SET @strSql = @strSql + 'FROM  dbo.AdvanceFundRequest AS Afr '
			SET @strSql = @strSql + 'INNER JOIN dbo.agragreement_fct AS af ON af.foldernumber = Afr.PropNumber '
			SET @strSql = @strSql + 'LEFT OUTER JOIN dbo.AdmPerson AS adm ON adm.FwkDomainUserId = Afr.SubmittedBy '
			SET @strSql = @strSql + 'LEFT OUTER JOIN dbo.AdmPerson AS admPI ON af.PrincipalInvestigatorId = admPI.Id '
			SET @strSql = @strSql + 'INNER JOIN dbo.FwkDomainOrganization AS fdo ON af.OrganizationId = fdo.Id '
			SET @strSql = @strSql + 'LEFT OUTER JOIN dbo.AdmResearchFinanceInfo AS arfC ON arfC.FwkDomainOrganizationId = fdo.Id AND arfC.RoleId = 11 '
			SET @strSql = @strSql + 'LEFT OUTER JOIN dbo.AdmPerson AS admCId ON admCId.Id = arfC.AdmPersonId '
			SET @strSql = @strSql + 'LEFT OUTER JOIN dbo.AdmResearchFinanceInfo AS arfD ON arfD.FwkDomainOrganizationId = fdo.Id AND arfD.RoleId = 7 '
			SET @strSql = @strSql + 'LEFT OUTER JOIN dbo.AdmPerson AS admDId ON admDId.Id = arfD.AdmPersonId '
			SET @strSql = @strSql + 'LEFT OUTER JOIN dbo.AdmResearchFinanceInfo AS arfG ON arfG.FwkDomainOrganizationId = fdo.Id AND arfG.RoleId = 3 '
			SET @strSql = @strSql + 'LEFT OUTER JOIN dbo.AdmPerson AS admGId ON admGId.Id = arfG.AdmPersonId '
			SET @strSql = @strSql +	'INNER JOIN dbo.UserResourceCache AS urc ON af.ResourceId = urc.ResourceId and urc.resourcetypeID = 1 and urc.fwkdomainuserid = ' + CAST(@fwkDomainUserId AS NVARCHAR(10)) 					  
			SET @strSql = @strSql +	')'
			SET @strSql = @strSql +	' SELECT * FROM PROPGrid WHERE ROWNUM BETWEEN ' +  CAST((@PageSize*@PageStartIndex) + 1 AS VARCHAR(10)) + ' AND ' + CAST(@PageSize*@PageEndIndex AS VARCHAR(10))

			--PRINT(@strSql)
			EXEC(@strSql)
END




GO